1 Imports System.Data.OleDb
2 Public Class frmItem
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New OleDbConnection(cs)
7 con.Open()
8 Dim OleDb As String = ("SELECT MAX(ItemID) FROM Dish")
9 cmd = New OleDbCommand(OleDb)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtItemID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtItemID.Text = Num.ToString
17 End If
18 con.Close()
19 con.Dispose()
20 Catch ex As Exception
21 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
22 End Try
23 End Sub
24 Sub fillCombo()
25 Try
26 Dim CN As New OleDbConnection(cs)
27 CN.Open()
28 adp = New OleDbDataAdapter()
29 adp.SelectCommand = New OleDbCommand("SELECT distinct RTRIM(CategoryName) FROM Category order by 1", CN)
30 ds = New DataSet("ds")
31 adp.Fill(ds)
32 Dim dtable As DataTable = ds.Tables(0)
33 cmbCategory.Items.Clear()
34 For Each drow As DataRow In dtable.Rows
35 cmbCategory.Items.Add(drow(0).ToString())
36 Next
37 Catch ex As Exception
38 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
39 End Try
40 End Sub
41 Sub fillKitchen()
42 Try
43 Dim CN As New OleDbConnection(cs)
44 CN.Open()
45 adp = New OleDbDataAdapter()
46 adp.SelectCommand = New OleDbCommand("SELECT distinct RTRIM(Kitchenname) FROM Kitchen order by 1", CN)
47 ds = New DataSet("ds")
48 adp.Fill(ds)
49 Dim dtable As DataTable = ds.Tables(0)
50 cmbKitchen.Items.Clear()
51 For Each drow As DataRow In dtable.Rows
52 cmbKitchen.Items.Add(drow(0).ToString())
53 Next
54 Catch ex As Exception
55 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
56 End Try
57 End Sub
58 Sub fillInventoryType()
59 Try
60 Dim CN As New OleDbConnection(cs)
61 CN.Open()
62 adp = New OleDbDataAdapter()
63 adp.SelectCommand = New OleDbCommand("SELECT distinct RTRIM(Type) FROM InventoryType order by 1", CN)
64 ds = New DataSet("ds")
65 adp.Fill(ds)
66 Dim dtable As DataTable = ds.Tables(0)
67 cmbInventoryType.Items.Clear()
68 For Each drow As DataRow In dtable.Rows
69 cmbInventoryType.Items.Add(drow(0).ToString())
70 Next
71 Catch ex As Exception
72 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
73 End Try
74 End Sub
75 Sub Reset()
76 cmbCategory.SelectedIndex = -1
77 cmbKitchen.SelectedIndex = -1
78 cmbInventoryType.SelectedIndex = -1
79 txtDiscount.Text = "0.00"
80 txtRate.Text = ""
81 txtSearchByDish.Text = ""
82 txtItemName.Text = ""
83 txtItemName.Focus()
84 btnSave.Enabled = True
85 btnUpdate.Enabled = False
86 btnDelete.Enabled = False
87 auto()
88 End Sub
89 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
90 Me.Close()
91 End Sub
92
93 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
94 If Len(Trim(txtItemName.Text)) = 0 Then
95 MessageBox.Show("Please enter item name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
96 txtItemName.Focus()
97 Exit Sub
98 End If
99 If Len(Trim(cmbCategory.Text)) = 0 Then
100 MessageBox.Show("Please select category", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
101 cmbCategory.Focus()
102 Exit Sub
103 End If
104 If Len(Trim(cmbKitchen.Text)) = 0 Then
105 MessageBox.Show("Please select kitchen/section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
106 cmbKitchen.Focus()
107 Exit Sub
108 End If
109 If Len(Trim(cmbInventoryType.Text)) = 0 Then
110 MessageBox.Show("Please select inventory type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
111 cmbInventoryType.Focus()
112 Exit Sub
113 End If
114 If Len(Trim(txtRate.Text)) = 0 Then
115 MessageBox.Show("Please enter rate", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
116 txtRate.Focus()
117 Exit Sub
118 End If
119 If txtDiscount.Text = "" Then
120 MessageBox.Show("Please enter discount %", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
121 txtDiscount.Focus()
122 Return
123 End If
124 Try
125 con = New OleDbConnection(cs)
126 con.Open()
127 Dim ct As String = "select DishName,InventoryType from Dish where DishName=@d1 and InventoryType=@d2"
128 cmd = New OleDbCommand(ct)
129 cmd.Parameters.AddWithValue("@d1", txtItemName.Text)
130 cmd.Parameters.AddWithValue("@d2", cmbInventoryType.Text)
131 cmd.Connection = con
132 rdr = cmd.ExecuteReader()
133
134 If rdr.Read() Then
135 MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
136 If (rdr IsNot Nothing) Then
137 rdr.Close()
138 End If
139 Return
140 End If
141 auto()
142 con = New OleDbConnection(cs)
143 con.Open()
144 Dim cb As String = "insert into Dish(ItemID,DishName,Category,Rate,Discount,InventoryType,Kitchen) VALUES (" & Val(txtItemID.Text) & ",@d1,@d2," & txtRate.Text & "," & txtDiscount.Text & ",@d3,@d4)"
145 cmd = New OleDbCommand(cb)
146 cmd.Connection = con
147 cmd.Parameters.AddWithValue("@d1", txtItemName.Text)
148 cmd.Parameters.AddWithValue("@d2", cmbCategory.Text)
149 cmd.Parameters.AddWithValue("@d3", cmbInventoryType.Text)
150 cmd.Parameters.AddWithValue("@d4", cmbKitchen.Text)
151 cmd.ExecuteReader()
152 con.Close()
153 Dim st As String = "added the new menu item '" & txtItemName.Text & "'"
154 LogFunc(lblUser.Text, st)
155 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
156 Getdata()
157 auto()
158 txtItemName.Text = ""
159 txtItemName.Focus()
160 Catch ex As Exception
161 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
162 End Try
163 End Sub
164
165 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
166 Try
167 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
168 DeleteRecord()
169 End If
170 Catch ex As Exception
171 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
172 End Try
173 End Sub
174 Private Sub DeleteRecord()
175
176 Try
177
178 Dim RowsAffected As Integer = 0
179 con = New OleDbConnection(cs)
180 con.Open()
181 Dim cl As String = "select ItemID from Dish,RestaurantBillingItems where Dish.ItemID=RestaurantBillingItems.Item_Id and ItemID=@d1"
182 cmd = New OleDbCommand(cl)
183 cmd.Connection = con
184 cmd.Parameters.AddWithValue("@d1", Val(txtItemID.Text))
185 rdr = cmd.ExecuteReader()
186 If rdr.Read Then
187 MessageBox.Show("Unable to delete..Already in use in Billing", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
188 If Not rdr Is Nothing Then
189 rdr.Close()
190 End If
191 Exit Sub
192 End If
193 con.Close()
194 con = New OleDbConnection(cs)
195 con.Open()
196 Dim cl1 As String = "select ItemID from Dish,KOTGenerationItems where Dish.ItemID=KOTGenerationItems.Item_Id and ItemID=@d1"
197 cmd = New OleDbCommand(cl1)
198 cmd.Connection = con
199 cmd.Parameters.AddWithValue("@d1", Val(txtItemID.Text))
200 rdr = cmd.ExecuteReader()
201 If rdr.Read Then
202 MessageBox.Show("Unable to delete..Already in use in Billing", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
203 If Not rdr Is Nothing Then
204 rdr.Close()
205 End If
206 Exit Sub
207 End If
208 con.Close()
209 con = New OleDbConnection(cs)
210 con.Open()
211 Dim cq As String = "delete from Dish where ItemID=@d1"
212 cmd = New OleDbCommand(cq)
213 cmd.Connection = con
214 cmd.Parameters.AddWithValue("@d1", Val(txtItemID.Text))
215 RowsAffected = cmd.ExecuteNonQuery()
216 If RowsAffected > 0 Then
217 Dim st As String = "deleted the menu item '" & txtItemName.Text & "'"
218 LogFunc(lblUser.Text, st)
219 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
220 Getdata()
221 Reset()
222 Else
223 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
224 Reset()
225 End If
226 If con.State = ConnectionState.Open Then
227 con.Close()
228
229 End If
230 Catch ex As Exception
231 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
232 End Try
233 End Sub
234
235 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
236 Try
237 If Len(Trim(txtItemName.Text)) = 0 Then
238 MessageBox.Show("Please enter item name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
239 txtItemName.Focus()
240 Exit Sub
241 End If
242 If Len(Trim(cmbCategory.Text)) = 0 Then
243 MessageBox.Show("Please select category", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
244 cmbCategory.Focus()
245 Exit Sub
246 End If
247 If Len(Trim(cmbKitchen.Text)) = 0 Then
248 MessageBox.Show("Please select kitchen/section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
249 cmbKitchen.Focus()
250 Exit Sub
251 End If
252 If Len(Trim(cmbInventoryType.Text)) = 0 Then
253 MessageBox.Show("Please select inventory type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
254 cmbInventoryType.Focus()
255 Exit Sub
256 End If
257 If Len(Trim(txtRate.Text)) = 0 Then
258 MessageBox.Show("Please enter rate", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
259 txtRate.Focus()
260 Exit Sub
261 End If
262 If txtDiscount.Text = "" Then
263 MessageBox.Show("Please enter discount %", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
264 txtDiscount.Focus()
265 Return
266 End If
267 con = New OleDbConnection(cs)
268 con.Open()
269 Dim cb As String = "update Dish set DishName=@d1,Category=@d2,Rate=" & txtRate.Text & ",Discount=" & txtDiscount.Text & ",InventoryType=@d4,Kitchen=@d5 where ItemID=" & txtItemID.Text & ""
270 cmd = New OleDbCommand(cb)
271 cmd.Connection = con
272 cmd.Parameters.AddWithValue("@d1", txtItemName.Text)
273 cmd.Parameters.AddWithValue("@d2", cmbCategory.Text)
274 cmd.Parameters.AddWithValue("@d4", cmbInventoryType.Text)
275 cmd.Parameters.AddWithValue("@d5", cmbKitchen.Text)
276 cmd.ExecuteReader()
277 con.Close()
278 Dim st As String = "updated the menu item '" & txtItemName.Text & "' details"
279 LogFunc(lblUser.Text, st)
280 MessageBox.Show("Successfully updated", "Item Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
281 btnUpdate.Enabled = False
282 Getdata()
283 Catch ex As Exception
284 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
285 End Try
286 End Sub
287 Public Sub Getdata()
288 Try
289 con = New OleDbConnection(cs)
290 con.Open()
291 cmd = New OleDbCommand("SELECT ItemID, RTRIM(DishName), RTRIM(Category),RTRIM(Kitchen),RTRIM(InventoryType), Rate,Discount from Dish order by DishName", con)
292 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
293 dgw.Rows.Clear()
294 While (rdr.Read() = True)
295 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
296 End While
297 con.Close()
298 Catch ex As Exception
299 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
300 End Try
301 End Sub
302 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
303 Reset()
304 End Sub
305 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
306 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
307 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
308 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
309 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
310 End If
311 Dim b As Brush = SystemBrushes.ControlText
312 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
313
314 End Sub
315
316 Private Sub frmItem_KeyDown(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown
317 If e.KeyCode = Keys.Enter Then
318 Me.SelectNextControl(Me.ActiveControl, True, True, True, False) 'for Select Next Control
319 End If
320 End Sub
321
322 Private Sub frmDish_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
323 Getdata()
324 fillCombo()
325 fillKitchen()
326 fillInventoryType()
327 End Sub
328
329 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
330 Try
331 If dgw.Rows.Count > 0 Then
332 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
333 txtItemID.Text = dr.Cells(0).Value.ToString()
334 txtItemName.Text = dr.Cells(1).Value.ToString()
335 cmbCategory.Text = dr.Cells(2).Value.ToString()
336 cmbKitchen.Text = dr.Cells(3).Value.ToString()
337 cmbInventoryType.Text = dr.Cells(4).Value.ToString()
338 txtRate.Text = dr.Cells(5).Value.ToString()
339 txtDiscount.Text = dr.Cells(6).Value.ToString()
340 btnUpdate.Enabled = True
341 btnDelete.Enabled = True
342 btnSave.Enabled = False
343 End If
344 Catch ex As Exception
345 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
346 End Try
347 End Sub
348
349 Private Sub txtServiceTax_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtRate.KeyPress
350 Dim keyChar = e.KeyChar
351
352 If Char.IsControl(keyChar) Then
353 'Allow all control characters.
354 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
355 Dim text = Me.txtRate.Text
356 Dim selectionStart = Me.txtRate.SelectionStart
357 Dim selectionLength = Me.txtRate.SelectionLength
358
359 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
360
361 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
362 'Reject an integer that is longer than 16 digits.
363 e.Handled = True
364 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
365 'Reject a real number with two many decimal places.
366 e.Handled = False
367 End If
368 Else
369 'Reject all other characters.
370 e.Handled = True
371 End If
372 End Sub
373
374 Private Sub txtFirstName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearchByDish.TextChanged
375 Try
376 con = New OleDbConnection(cs)
377 con.Open()
378 cmd = New OleDbCommand("SELECT ItemID, RTRIM(DishName), RTRIM(Category),RTRIM(Kitchen),RTRIM(InventoryType), Rate,Discount from Dish where DishName like '%" & txtSearchByDish.Text & "%' order by DishName", con)
379 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
380 dgw.Rows.Clear()
381 While (rdr.Read() = True)
382 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6))
383 End While
384 con.Close()
385 Catch ex As Exception
386 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
387 End Try
388 End Sub
389
390 Private Sub txtDiscount_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtDiscount.KeyPress
391 Dim keyChar = e.KeyChar
392
393 If Char.IsControl(keyChar) Then
394 'Allow all control characters.
395 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
396 Dim text = Me.txtDiscount.Text
397 Dim selectionStart = Me.txtDiscount.SelectionStart
398 Dim selectionLength = Me.txtDiscount.SelectionLength
399
400 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
401
402 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
403 'Reject an integer that is longer than 16 digits.
404 e.Handled = True
405 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
406 'Reject a real number with two many decimal places.
407 e.Handled = False
408 End If
409 Else
410 'Reject all other characters.
411 e.Handled = True
412 End If
413 End Sub
414
415 End Class
